﻿/*
SELECT    top 100 percent  F.cItem AS TTP1, s.CodeID, s.hName,s.hrccrt
FROM        dbo.dtCity AS s  with (nolock) CROSS apply dbo.ifs_SplitIn2RowsX(s.hrccrt, '.') AS F
ORDER BY Rank() OVER (ORDER BY F.cItem)
*/
CREATE FUNCTION [dbo].[ifs_SplitIn2RowsX]
    (
	  @S nvarchar(MAX), 
      @pInChrSeparator nchar(1) = N','
	)
RETURNS TABLE
AS RETURN
WITH T(R) AS (SELECT CONVERT(XML,N'<root><V i="'+REPLACE(CONVERT(nvarchar(MAX),@S),@pInChrSeparator,N'"/><V i="')+N'"/></root>'))
SELECT CONVERT(nvarchar,chs.ch.value(N'@i',N'nvarchar(200)')) as cItem
FROM T CROSS APPLY R.nodes(N'/root/V') chs(ch);


/*
WITH T(R) AS (SELECT CONVERT(XML,'<root><V i="'+REPLACE(CONVERT(VARCHAR(MAX),@S),@pInChrSeparator,'"/><V i="')+'"/></root>'))
SELECT CONVERT(varchar,chs.ch.value('@i','varchar(200)')) as cItem
FROM T CROSS APPLY R.nodes('/root/V') chs(ch);
*/


